package com.api.gbhg.huangwm.dao;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.customization.commons.LogTool;
import weaver.conn.RecordSet;
import weaver.general.Util;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author Huangwm
 * @Date 2022-04-21
 * @Version 1.0
 * @Description TODO 报销查询页面列表数据操作
 **/
public class ReimbursementListDao {
    private LogTool logTool = new LogTool("/log/gbhg/huangwm/dao//ReimbursementListDao",false);
    private ExpendBudgetDButils dButils = new ExpendBudgetDButils();

    /**
     * 获取单位审核列表
     * @param params
     * @return
     * @throws Exception
     */
    public List<Map<String,String>> getDwshList(Map<String,Object> params) throws Exception{
        logTool.writeLog("单位审核报销列表查询");
        logTool.writeLog(JSON.toJSONString(params));
        RecordSet rs = new RecordSet();
        RecordSet rs2 = new RecordSet();
        List<Map<String,String>> result = new ArrayList<>();
        if(!params.containsKey("requestids")){
            throw new Exception("缺少必要参数requestids");
        }
        if(!params.containsKey("action")){
            throw new Exception("缺少必要参数action");
        }
        String requestids = params.get("requestids").toString();
        String action = params.get("action").toString();//todo done 代表待办和已办
        if(requestids.isEmpty()){
            throw new Exception("requestids为空");
        }
        String requestname = params.getOrDefault("requestname","").toString();
        String jbr = params.getOrDefault("jbr","").toString();
        String jbks = params.getOrDefault("jbks","").toString();
        String sqbxje1 = params.getOrDefault("sqbxje1","").toString();
        String sqbxje2 = params.getOrDefault("sqbxje2","").toString();
        if(sqbxje2.isEmpty() || Double.parseDouble(sqbxje2)==0.00) sqbxje2=sqbxje1;
        int pageNumber= Integer.parseInt(params.get("pageNumber").toString());
        int pageSize= Integer.parseInt(params.get("pageSize").toString());
        pageNumber=(pageNumber-1)*pageSize;
        String sql = "select lcid,sqbxje,wr.requestid,wr.creater as jbr,wr.createdate as tdrq,requestname,requestmark as lcbh ,hr.departmentid,hr.subcompanyid1\n" +
                ",wr.workflowid,wn.nodename from workflow_requestbase wr right join(\n" +
                "select lcid,sum(sqbxje) as sqbxje from uf_bxsjjl group by lcid\n" +
                ")t on wr.requestid=t.lcid\n" +
                "left join HrmResource hr on hr.id=wr.creater\n" +
                "left join workflow_nodebase wn on wn.id=wr.currentnodeid\n" +
                "where t.lcid in("+requestids+") \n";
        if(!requestname.isEmpty()) sql+=" and requestname like '%"+requestname+"%' \n";
        if(!jbks.isEmpty()) sql+=" and departmentid = "+jbks+" \n";
        if(!jbr.isEmpty()) sql+=" and requestname creater="+jbr+" \n";
        if(!sqbxje1.isEmpty()) sql+=" and sqbxje between "+sqbxje1+" and "+sqbxje2+"\n";

        sql += " order by wr.requestid desc offset "+pageNumber+" rows fetch next "+pageSize+" rows only";
        logTool.writeLog(sql);
        if(!rs.execute(sql)){
            throw new SQLException("查询关联部门审批数据异常");
        }
        String[] columnName = rs.getColumnName();//查询出来的字段
        int[] columnType = rs.getColumnType();//字段的类型
        int index = 1;
        while (rs.next()){
            Map<String,String> data = new HashMap<>();
            for (int i = 0; i < columnName.length; i++) {
                if(columnName[i].equals("jbr")){
                    data.put(columnName[i],dButils.getUserNameById(rs.getString(columnName[i])));
                }else if(columnName[i].equals("departmentid")){
                    data.put(columnName[i],dButils.getDepartmentNameById(rs.getString(columnName[i])));
                }else if(columnName[i].equals("subcompanyid1")){
                    data.put(columnName[i],dButils.getSubcompanyNameById(rs.getString(columnName[i])));
                }else {
                    data.put(columnName[i], rs.getString(columnName[i]));
                }
            }
            data.put("index",index+"");
            index ++;
            result.add(data);
        }
        return result;
    }

    public void runBefore(String requestid) {
        new RecordSet().execute(" delete from uf_bxjl_kj where lcid="+requestid);
        new RecordSet().execute(" delete from uf_bxjl_kj_dt1 where lcid="+requestid);
        new RecordSet().execute(" delete from uf_bxjl_kj_dt2 where lcid="+requestid);
    }

    public void runAfter(String requestid){
        new RecordSet().execute("update uf_bxjl_kj set sqbxje=(select sum(sqbxje) from uf_bxjl_kj_dt1 where uf_bxjl_kj_dt1.lcid=uf_bxjl_kj.lcid)," +
                "tybxje=(select sum(tybxje) from uf_bxjl_kj_dt1 where uf_bxjl_kj_dt1.lcid=uf_bxjl_kj.lcid) where lcid="+requestid);
    }
    //记录主表
    public String sql1 = "insert into uf_bxjl_kj(requestid,lcid,jbr,jbks,jbbm,sqrq,bxdbh,shkj,shrq,dqjdid,dqjdlx)values(?,?,?,?,?,?,?,?,?,?,?)";
    //记录明细1
    public String sql2 = "insert into uf_bxjl_kj_dt1(lcid,mxid,fysx,zflx,ysxm,bmjjzcfl,zfjjzcfl,bxkmdm,zcqd,ysly,zcgnkm,zfl,sqbxje,tybxje,kmjxj,dygnkmbid,fzysrfl,zcdfczbkxm)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    //记录明细2
    public String sql3 = "insert into uf_bxjl_kj_dt2(lcid,mxid,zffs,zzlx,fkyx,fkzh,sqzfje,tyzfje,gwkskje)values(?,?,?,?,?,?,?,?,?)";

    /**
     * 同步各流程费用报销明细中的支出渠道到uf_bxjl_kj_dt1
     */
    public void runAfter2(){
        ProjectOperationDao dao = new ProjectOperationDao();
        RecordSet rs1 = new RecordSet();
        RecordSet rs2 = new RecordSet();
        RecordSet rs3 = new RecordSet();
        RecordSet rs4 = new RecordSet();
        List<List> datas = new ArrayList<>();
        rs1.execute(" select id,lcid,mxid from uf_bxjl_kj_dt1  ");
        while (rs1.next()){
            List data = new ArrayList();
            String dtid = rs1.getString("id");
            String requestid = rs1.getString("lcid");
            System.err.println("requestid="+requestid);
            String tablename = dao.getWorkflowTabNameByRequestid(rs2,requestid);
            String sql = "select dt.id,ysly,xzkmdm,kmdm,kmjxj,hpssjid,fzysrfl,zcdfczbkxm,isnull(cxje,0) as cxje \n" +
                    "from "+tablename+"_dt1 dt\n" +
                    "left join uf_gxmdygnkm on uf_gxmdygnkm.id=dt.xzkmdm\n" +
                    "left join uf_srysfl on dt.fzysrfl=uf_srysfl.id\n" +
                    "left join uf_fzysrxm on dt.zcdfczbkxm=uf_fzysrxm.id\n" +
                    "where dt.id="+rs1.getString("mxid");
            if(rs3.execute(sql)){
                rs3.next();
                String ysly = Util.null2String(rs3.getString("ysly"));//预算来源 0-财政拨款 1-非财政
                String xzkmdm = Util.null2String(rs3.getString("xzkmdm"));//选择科目代码 关联uf_gxmdygnkm表id
                String kmdm = Util.null2String(rs3.getString("kmdm"));//财政拨款科目代码 关联uf_gxmdygnkm表
                String kmjxj = Util.null2String(rs3.getString("kmjxj"));//科目级下级 0-科目级 1-科目下级
                String zyczbkid = Util.null2String(rs3.getString("hpssjid")).equals("")?"":rs3.getString("hpssjid").split("_")[1];//核批的财政收入表id
                String fzysrfl = Util.null2String(rs3.getString("fzysrfl"));//非中央收入分类
                String zcdfczbkxm = Util.null2String(rs3.getString("zcdfczbkxm"));//地方财政拨款项目
                Double cxje =  rs3.getDouble("cxje");//冲销金额
                data.add(kmjxj);
                data.add(zyczbkid);
                data.add(fzysrfl);
                data.add(zcdfczbkxm);
                data.add(cxje);
                data.add(dtid);
                datas.add(data);
            }else{
                sql = "select dt.id,ysly,xzkmdm,kmdm,kmjxj,dygnkmbid,fzysrfl,zcdfczbkxm,isnull(bccxjkje,0) as bccxjkje,hpssjid \n" +
                        "from "+tablename+" dt\n" +
                        "left join uf_gxmdygnkm on uf_gxmdygnkm.id=dt.xzkmdm\n" +
                        "left join uf_srysfl on dt.fzysrfl=uf_srysfl.id\n" +
                        "left join uf_fzysrxm on dt.zcdfczbkxm=uf_fzysrxm.id\n" +
                        "where dt.id="+rs1.getString("mxid");
                if(rs3.execute(sql) && rs3.next()) {
                    String ysly = Util.null2String(rs3.getString("ysly"));//预算来源 0-财政拨款 1-非财政
                    String xzkmdm = Util.null2String(rs3.getString("xzkmdm"));//选择科目代码 关联uf_gxmdygnkm表id
                    String kmdm = Util.null2String(rs3.getString("kmdm"));//财政拨款科目代码 关联uf_gxmdygnkm表
                    String kmjxj = Util.null2String(rs3.getString("kmjxj"));//科目级下级 0-科目级 1-科目下级
                    String zyczbkid = Util.null2String(rs3.getString("hpssjid")).equals("")?"":rs3.getString("hpssjid").split("_")[1];//核批的财政收入表id
                    String fzysrfl = Util.null2String(rs3.getString("fzysrfl"));//非中央收入分类
                    String zcdfczbkxm = Util.null2String(rs3.getString("zcdfczbkxm"));//地方财政拨款项目
                    Double bccxjkje = rs3.getDouble("bccxjkje");//冲销金额
                    String hpssjid = Util.null2String(rs3.getString("hpssjid"));//地方财政拨款项目
                    data.add(kmjxj);
                    data.add(zyczbkid);
                    data.add(fzysrfl);
                    data.add(zcdfczbkxm);
                    data.add(bccxjkje);
                    data.add(dtid);
                    datas.add(data);
                }
            }
        }

        try{
            String sql = "update uf_bxjl_kj_dt1 set kmjxj=?,dygnkmbid=?,fzysrfl=?,zcdfczbkxm=?,cxje=? where id=? ";
            boolean r = rs4.executeBatchSql(sql,datas);
            logTool.writeLog("插入数据结果 "+r);
            new RecordSet().execute("update uf_bxjl_kj_dt1 set dw=uf_jingfei_version.dlhsdw from uf_jingfei_version where uf_bxjl_kj_dt1.ysxm=uf_jingfei_version.lcjlsqjlid");
            new RecordSet().execute("UPDATE uf_bxjl_kj_dt1 SET dlhsdw=uf_jingfei_version.dlhsdw FROM dbo.uf_jingfei_version WHERE lcjlsqjlid=ysxm");
        }catch (Exception e){
            logTool.writeLog("插入数据SQL异常");
            logTool.writeLog(e.getMessage());
            e.printStackTrace();
        }
    }
}
